package org.ssp.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.ssp.base.ResultSetHanlder;
import org.ssp.dao.SchoolDao;
import org.ssp.pojo.School;
import org.ssp.util.DBUtil;



public class SchoolImpl implements SchoolDao {
 /*   //1，添加学校
	@Override
	public void addSchool(School school) {
		String sql="insert into school(name,content,rank) values(?,?,?);";

		DBUtil.update(sql, school.getName(),school.getContent(),school.getRank());
	
	}*/
	/**
	 * add school事务处理：
	 * insert into school values()
	 * insert into school_major values(id,fieldname)
	 * 
	 * 
	 * @param school
	 */
	@Override
	public void addSchool(School school,ArrayList<String[]> list) {
		Connection conn=DBUtil.getConnetion();
		ResultSet rs=null;
		PreparedStatement ps=null;
		try {
			conn.setAutoCommit(false);
			String sql="insert into school(name,content,rank) values(?,?,?);";
			ps=conn.prepareStatement(sql);
			ps.setString(1, school.getName());
			ps.setString(2, school.getContent());
			ps.setInt(3, school.getRank());
			ps.execute();
			sql="select last_insert_id()";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			int id=0;
			if(rs.next()){
				id=rs.getInt(1);
			}
			sql="insert into school_major(schoolid,fieldname) values(?,?)";
            ps=conn.prepareStatement(sql);
			for(String[] str:list){
				ps.setInt(1, id);
				ps.setString(2, str[0]);
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			DBUtil.update(sql, school.getName(),school.getContent(),school.getRank());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			DBUtil.RelaseSource(rs, ps, conn);
		}
	
	}
	@Override
	public void addSchool2(School school) {
		System.out.println("插入......");
		String sql="insert into midschool(name,content) values(?,?);";
		DBUtil.update(sql, school.getName(),school.getContent());
	}
    //2，查询全部学校
	@Override
	public ArrayList<School> findAll(int offset, int rows) {
		String sql="select * from school order by rank asc  limit ?,? ;";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
						school.setRank(rs.getInt("rank"));
						list.add(school);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, offset,rows);
	}
    //3，学校总数
	@Override
	public int findAll() {
		String sql="select * from school;";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
				}
				return total;
			}
		}, null);
	}
	
	@Override
	public ArrayList<School> findAll2(int offset, int rows) {
		String sql="select * from midschool  limit ?,? ;";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
						list.add(school);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, offset,rows);
	}
    //3，学校总数
	@Override
	public int findAll2() {
		String sql="select * from midschool;";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
				}
				return total;
			}
		}, null);
	}
	@Override
	public School findByName(String name) {
		String sql="select * from school where name=?;";
		return (School)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				School school=null;
				try {
					if(rs.next()){
						school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
						school.setRank(rs.getInt("rank"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return school;
			}
		}, name);
	}
	@Override
	public School findByName2(String name) {
		String sql="select * from midschool where name=?;";
		return (School)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				School school=null;
				try {
					if(rs.next()){
						school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return school;
			}
		}, name);
	}
	//学校已经存在时做更新
	@Override
	public void update(School school) {
		System.out.println("更新.....");
		String sql="update school set content=?,rank=? where name=?;";
		DBUtil.update(sql,school.getContent(),school.getRank(),school.getName());
	}
	//学校已经存在时做更新
	@Override
	public void update2(School school) {
		System.out.println("更新.....");
		String sql="update midschool set content=? where name=?;";
		DBUtil.update(sql,school.getContent(),school.getName());
	}
	//批量删除
	@Override
	public void deleteAll() {
		String sql="TRUNCATE TABLE school;;";
		DBUtil.update(sql, null);
		
	}
	//批量删除
	@Override
	public void deleteAll2() {
		String sql="TRUNCATE TABLE midschool;;";
		DBUtil.update(sql, null);
		
	}
	@Override
	public void deleteCollege(int id) {
		String sql="delete from school where id=?";
		DBUtil.update(sql, id);
	}
	@Override
	public void updateCollege(School school) {
		String sql="update school set name=?,content=?,rank=? where id=? ";
		DBUtil.update(sql, school.getName(),school.getContent(),school.getRank(),school.getId());
		
	}
	@Override
	public void updateRank(int id, int rank) {
		String sql="update school set rank=? where id=?";
		DBUtil.update(sql, rank,id);
	}
	//全部大学
	@Override
	public ArrayList<School> findAllSchool() {
		String sql="select * from school";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
						school.setRank(rs.getInt("rank"));
						list.add(school);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, null);
	}
	
	/**
	 * 增加专业时的事务处理：1)insert into major 2)insert into school_major(),批量插入
	 * 
	 * 批量插入：1)在事物中插入 2)使用jdbc的批处理
	 * @param name
	 * @param list
	 * @return
	 */
	@Override
	public int[] addMajor(String name,ArrayList<School> list) {
		Connection conn=DBUtil.getConnetion();
		PreparedStatement ps=null;
		String sql=null;
		int[] count = null;
		try {
			conn.setAutoCommit(false);
			sql="insert into major(name) values(?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.addBatch();
			ps.executeBatch();
			sql="insert into school_major(schoolid,fieldname) values(?,?);";
			ps=conn.prepareStatement(sql);
			for(School school:list){
				ps.setInt(1, school.getId());
				ps.setString(2, name);
				ps.addBatch();
			}
			count=ps.executeBatch();
			conn.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally {
			DBUtil.RelaseSource(null, ps, conn);
		}
		return count;
	}
	//所有专业
	@Override
	public ArrayList<String[]> findAllMajor() {
		String sql="select * from major";
		return (ArrayList<String[]>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<String[]> list=new  ArrayList<String[]>();
				try {
					while(rs.next()){
						String[] strs={rs.getString("name"),String.valueOf(rs.getInt("id"))};
						list.add(strs);

					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, null);
	}
	@Override
	public ArrayList<School> getMajorRank(String fieldname,int offset,int rows) {
		String sql="select t1.id,t1.name,t1.content,t2.filevalue from school as t1, school_major as t2 where t1.id=t2.schoolid and t2.fieldname=? limit ?,?; ";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt(1));
						school.setName(rs.getString(2));
						school.setContent(rs.getString(3));
						school.setRank(rs.getInt(4));
						list.add(school);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		},fieldname,offset,rows);
	}
	@Override
	public void updateMajorRank(int id, String fieldname, String filevalue) {
		String sql="update school_major set filevalue=? where schoolid=? and fieldname=?";
		DBUtil.update(sql,filevalue,id,fieldname );
		
		
	}
	/**
	 * 
	 * 更新专业的名字事务
	 * update major set name=? where name=?;
	 * update school_major name=? where fieldname=?
	 * 
	 * 
	 * 
	 * 
	 * @param name
	 */
	@Override
	public int updateMajorName(String oldname,String newname) {
		int count=0;
		Connection conn=DBUtil.getConnetion();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		PreparedStatement ps=null;
		try {
			conn.setAutoCommit(false);
			String sql="update major set name=? where name=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, newname);
			ps.setString(2, oldname);
			ps.executeUpdate();
			
			sql="update school_major set fieldname=? where fieldname=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, newname);
			ps.setString(2, oldname);
			count=ps.executeUpdate();
			System.out.println(count);
			conn.commit();
			System.out.println("成功");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			DBUtil.RelaseSource(null, ps, conn);
		}
		return count;
	
	}
	/**
	 * 删除专业的事务
	 * delete from major where name=?
	 * delete from school_major where fieldnmae=?
	 * 
	 * @param name
	 */
	@Override
	public int deleteMajor(String name) {
		int count=0;
		Connection conn=DBUtil.getConnetion();
		PreparedStatement ps=null;
		try {
			conn.setAutoCommit(false);
			String sql="delete from major where name=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.executeUpdate();
			System.out.println("1");
			
			sql="delete from school_major where fieldname=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			count=ps.executeUpdate();
            System.out.println("2");
			conn.commit();
			System.out.println("commit");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return count;
	}
	@Override
	public void updateMidschool(School school) {
		String sql="update midschool set name=?,content=? where id=?";
		DBUtil.update(sql, school.getName(),school.getContent(),school.getId());
		
	}
	@Override
	public void deleteMidschool(int id) {
		String sql="delete from midschool where id=?";
		DBUtil.update(sql, id);
		
	}
	//前端，大学列表
	@Override
	public ArrayList<School> getAllData() {
		String sql="select * from school order by rank asc;";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setRank(rs.getInt("rank"));
						list.add(school);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, null);
	}
	@Override
	public String getSchoolContent(int id) {
		String sql="select content from school where id=?;";
		return (String)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				String str=new String();
				try {
					if(rs.next()){
						str=rs.getString("content");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return str;
			}
		}, id);
	}
	public ArrayList<School> majorRank(String fieldname) {
		String sql="select t1.id,t1.name,t1.content,t2.filevalue from school as t1, school_major as t2 where t1.id=t2.schoolid and t2. fieldname=? order by t2.filevalue asc";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt(1));
						school.setName(rs.getString(2));
						school.setContent(rs.getString(3));
						school.setRank(rs.getInt(4));
						list.add(school);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		},fieldname);
	}
	@Override
	public ArrayList<School> getMidschool() {
		String sql="select * from midschool order by id  asc;";
		return (ArrayList<School>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<School> list=new ArrayList<School>();
				try {
					while(rs.next()){
						School school=new School();
						school.setId(rs.getInt("id"));
						school.setName(rs.getString("name"));
						school.setContent(rs.getString("content"));
						list.add(school);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, null);
	}
	@Override
	public String getMidSchoolContent(int id) {
		String sql="select content from midschool where id=?;";
		return (String)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				String str=new String();
				try {
					if(rs.next()){
						str=rs.getString("content");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return str;
			}
		}, id);
	}

}
